﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using CoreEx.Common.Extensions;
using LinFu.IoC.Configuration;
using LinFu.IoC;
using LinFu.IoC.Interfaces;
using LinFu.Reflection;

namespace CoreEx.Database.Query.Implementation
{
    [Implements(typeof(IQueryTranslator), ServiceName = "System.Data.SqlClient")]
    public class QueryTranslator : IQueryTranslator, IInitialize<IServiceRequestResult>
    {
        private string _providerName;
        private string quotePrefix;
        private string quoteSuffix;
        private DbProviderFactory _providerFactory;

        private readonly IDictionary<string, IDataParameter> _implicitParameters =
            new Dictionary<string, IDataParameter>();

        private IServiceContainer _serviceContainer;


        public ITranslatedQuery Translate(IExpression queryExpression)
        {
            _implicitParameters.Clear();

            var translatedQuery = _serviceContainer.GetService<ITranslatedQuery>();
            translatedQuery.Sql = Visit(queryExpression);
            translatedQuery.ImplicitParameters = _implicitParameters;
            return translatedQuery;
        }

        protected virtual string QuoteIdentifier(string value)
        {
            return string.Format("{0}{1}{2}", quotePrefix, value, quoteSuffix);
        }


        protected string Visit(IExpression expression)
        {
            if (expression == null)
                return string.Empty;
            
            Type expressionType = expression.GetType();

            if (typeof(ISelectExpression).IsAssignableFrom(expressionType))
                return VisitSelectExpression((ISelectExpression)expression);

            if (typeof(IExpressionList).IsAssignableFrom(expressionType))
                return VisitExpressionList((IExpressionList)expression);

            if (typeof(IColumnExpression).IsAssignableFrom(expressionType))
                return VisitColumnExpression((IColumnExpression)expression);

            if (typeof(ITableExpression).IsAssignableFrom(expressionType))
                return VisitTableExpression((ITableExpression)expression);

            if (typeof(IJoinExpression).IsAssignableFrom(expressionType))
                return VisitJoinExpression((IJoinExpression)expression);

            if (typeof(IBinaryExpression).IsAssignableFrom(expressionType))
                return VisitBinaryExpression((IBinaryExpression)expression);

            if (typeof(IAggregateExpression).IsAssignableFrom(expressionType))
                return VisitAggregateExpression((IAggregateExpression)expression);

            if (typeof(IConstantExpression).IsAssignableFrom(expressionType))
                return VisitConstantExpression((IConstantExpression)expression);
            
            if (typeof(IAliasExpression).IsAssignableFrom(expressionType))
                return VisitAliasExpression((IAliasExpression)expression);

            if (typeof(ICompositeExpression).IsAssignableFrom(expressionType))
                return VisitCompositeExpression((ICompositeExpression)expression);
            if (typeof(IExistsExpression).IsAssignableFrom(expressionType))
                return VisitExistsExpression((IExistsExpression)expression);
            if (typeof(IUnaryExpression).IsAssignableFrom(expressionType))
                return VisitUnaryExpression((IUnaryExpression)expression);

            if (typeof(ICaseExpression).IsAssignableFrom(expressionType))
                return VisitCaseExpression((ICaseExpression)expression);
            if (typeof(IWhenThenExpression).IsAssignableFrom(expressionType))
                return VisitWhenThenExpression((IWhenThenExpression)expression);
            
            if (typeof(IOrderByExpression).IsAssignableFrom(expressionType))
                return VisitOrderByExpression((IOrderByExpression)expression);
            if (typeof(IInExpression).IsAssignableFrom(expressionType))
                return VisitInExpression((IInExpression)expression);
            if (typeof(ICastExpression).IsAssignableFrom(expressionType))
                return VisitCastExpression((ICastExpression)expression);
            return null;
        }

        protected virtual string VisitCastExpression(ICastExpression castExpression)
        {
            string datatypeSyntax = null;

            if (castExpression.TargetType == typeof(string))            
                datatypeSyntax = "VARCHAR(MAX)";
            
            if (datatypeSyntax == null)
                throw new ArgumentOutOfRangeException("castExpression", "No conversion exists");

            return string.Format("CAST({0} AS {1})", Visit(castExpression.Target), datatypeSyntax);
        }


        protected virtual string VisitOrderByExpression(IOrderByExpression orderByExpression)
        {
            if (orderByExpression.Direction == SortDirection.Ascending)
                return string.Format("{0}", Visit(orderByExpression.Target));            
            return string.Format("{0} DESC", Visit(orderByExpression.Target));
        }

        protected virtual string VisitCaseExpression(ICaseExpression caseExpression)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine();
            sb.AppendFormat(1,"(CASE");
            sb.AppendLine();
            foreach (var whenThenExpression in caseExpression.WhenExpressions)
            {
                sb.AppendFormat(2, "{0}", Visit(whenThenExpression));
                sb.AppendLine();
            }

            sb.AppendFormat(1," ELSE {0} END)", Visit(caseExpression.ElseExpression));

            return sb.ToString();
        }

        protected virtual string VisitWhenThenExpression(IWhenThenExpression whenThenExpression)
        {
            return string.Format(" WHEN {0} THEN {1}", Visit(whenThenExpression.When), Visit(whenThenExpression.Then));
        }



        protected virtual string VisitUnaryExpression(IUnaryExpression expression)
        {
            if (expression.ExpressionType == UnaryExpressionType.Not)
                return string.Format("(NOT {0})", Visit(expression.Operand));
            if (expression.ExpressionType == UnaryExpressionType.Distinct)
                return string.Format("DISTINCT {0}", Visit(expression.Operand));

            throw new NotSupportedException(string.Format("ExpressionType {0} is not supported",
                                                          expression.ExpressionType));
        }


        protected virtual string VisitSelectExpression(ISelectExpression expression)
        {
            if (expression.SkipCount > 0)
                return BuildPagingStatement(expression);

            var sb = new StringBuilder();

            if (expression.ProjectionExpression != null)
            {
                if (expression.TakeCount > 0)
                {
                    IUnaryExpression unaryExpression = expression.ProjectionExpression as IUnaryExpression;
                    if (unaryExpression != null && unaryExpression.ExpressionType == UnaryExpressionType.Distinct)
                    {
                        sb.AppendFormat("SELECT DISTINCT TOP ({0}) ", expression.TakeCount);
                        sb.AppendFormat(1, "{0} ", Visit(unaryExpression.Operand));
                    }
                    else
                    {
                        sb.AppendFormat("SELECT TOP ({0}) ", expression.TakeCount);
                        sb.AppendFormat(1, "{0} ", Visit(expression.ProjectionExpression));
                    }
                }
                else
                {
                    sb.Append("SELECT ");
                    sb.AppendLine();
                    sb.AppendFormat(1, "{0} ", Visit(expression.ProjectionExpression));
                }
            }

            if (expression.IntoExpression != null)
            {                
                sb.AppendFormat("INTO {0} ", Visit(expression.IntoExpression));
            }

            if (expression.FromExpression != null)
            {
                sb.AppendLine();
                sb.AppendLine("FROM ");
                sb.AppendFormat(1,"{0} ", Visit(expression.FromExpression));
            }

            if (expression.WhereExpression != null)
            {
                sb.AppendLine();
                sb.AppendLine("WHERE ");
                sb.AppendFormat(1, "{0} ", Visit(expression.WhereExpression));
            }

            if (expression.OrderByExpression != null)
            {
                sb.AppendLine();
                sb.AppendLine("ORDER BY ");
                sb.AppendFormat(1, "{0} ", Visit(expression.OrderByExpression));
            }


            if (expression.IsSubQuery)
                return string.Format("({0})", sb);

            return sb.ToString().Trim();
        }

        protected virtual string BuildPagingStatement(ISelectExpression selectExpression)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM (SELECT {0}, ", Visit(selectExpression.ProjectionExpression));
            sb.Append("ROW_NUMBER() OVER (ORDER BY ");
            if (selectExpression.OrderByExpression != null)
                sb.Append(Visit(selectExpression.OrderByExpression));
            else
                sb.Append(Visit(selectExpression.ProjectionExpression));
            sb.Append(") AS [ROW_NUMBER] ");

            if (selectExpression.FromExpression != null)
                sb.AppendFormat("FROM {0} ", Visit(selectExpression.FromExpression));

            if (selectExpression.WhereExpression != null)
                sb.AppendFormat("WHERE {0} ", Visit(selectExpression.WhereExpression));
            sb.Append(") AS __numbered__result ");

            if (selectExpression.TakeCount > 0)
                sb.AppendFormat("WHERE [ROW_NUMBER] BETWEEN {0} AND {1} ", selectExpression.SkipCount,
                                selectExpression.SkipCount + selectExpression.TakeCount);
            else
                sb.AppendFormat("WHERE [ROW_NUMBER] > {0} ", selectExpression.SkipCount);
                            
            sb.Append("ORDER BY [ROW_NUMBER]");

            if (selectExpression.IsSubQuery)
                return string.Format("({0})", sb);

            return sb.ToString().Trim();
        }

        private ConstantExpression FindConstantExpressionFromBinaryExpression(IBinaryExpression binaryExpression)
        {
            ConstantExpression constantExpression = binaryExpression.LeftExpression as ConstantExpression;
            if ((IExpression)constantExpression == null)
                constantExpression = binaryExpression.RightExpression as ConstantExpression;

            return constantExpression;
        }


        protected virtual string VisitBinaryExpression(IBinaryExpression expression)
        {
            if (expression.ExpressionType == BinaryExpressionType.Contains)
            {
                var constantExpression = FindConstantExpressionFromBinaryExpression(expression);
                if (!constantExpression.Value.ToString().StartsWith("%"))
                constantExpression.Value = string.Format("%{0}%", constantExpression.Value);                
            }

            if (expression.ExpressionType == BinaryExpressionType.StartsWith)
            {
                var constantExpression = FindConstantExpressionFromBinaryExpression(expression);
                if (!constantExpression.Value.ToString().EndsWith("%"))
                    constantExpression.Value = string.Format("{0}%", constantExpression.Value);
            }

            if (expression.ExpressionType == BinaryExpressionType.EndsWith)
            {
                var constantExpression = FindConstantExpressionFromBinaryExpression(expression);
                if (!constantExpression.Value.ToString().StartsWith("%"))
                constantExpression.Value = string.Format("%{0}", constantExpression.Value);
            }

            var leftExpression = Visit(expression.LeftExpression);
            var rightExpression = Visit(expression.RightExpression);
            if (leftExpression == null)
            {
                if (expression.ExpressionType == BinaryExpressionType.Equal)
                    return string.Format("({0} IS NULL)", rightExpression);
                if (expression.ExpressionType == BinaryExpressionType.NotEqual)
                    return string.Format("({0} IS NOT NULL)", rightExpression);
            }
            if (rightExpression== null)
            {
                if (expression.ExpressionType == BinaryExpressionType.Equal)
                    return string.Format("({0} IS NULL)", leftExpression);
                if (expression.ExpressionType == BinaryExpressionType.NotEqual)
                    return string.Format("({0} IS NOT NULL)", leftExpression);
            }
                
            
            return string.Format("({0}{1}{2})", leftExpression, GetBinaryOperator(expression),
                                 rightExpression);
        }

        protected virtual string VisitAggregateExpression(IAggregateExpression expression)
        {
            if (expression.ExpressionType == AggregateExpressionType.Count && expression.Target == null)
                return "COUNT(*)";
            
            return string.Format("{0}({1})", GetAggregateOperator(expression), Visit(expression.Target));
        }



        protected virtual string VisitExpressionList(IExpressionList expressionList)
        {
            if (expressionList.Expressions.Count() == 0)
                return string.Empty;
            
            return expressionList.Expressions.Select(e => Visit(e)).Aggregate((current, next) => current + ", " + next);
        }

        protected virtual string VisitAliasExpression(IAliasExpression expression)
        {
            return string.Format("{0} AS {1}", Visit(expression.Target), expression.Alias);
        }


        protected virtual string VisitCompositeExpression(ICompositeExpression expression)
        {
            return string.Format("{0} {1}", Visit(expression.LeftExpression), Visit(expression.RightExpression));
        }


        protected virtual string VisitTableExpression(ITableExpression expression)
        {
            string table;
            if (!string.IsNullOrEmpty(expression.TablePrefix))
                table = string.Format("{0}.{1}", expression.TablePrefix, QuoteIdentifier(expression.TableName));
            else
                table = QuoteIdentifier(expression.TableName);            
            return table;
        }


        protected virtual string VisitJoinExpression(IJoinExpression expression)
        {
            string keyWord = null;

            switch(expression.JoinType)
            {
                case JoinType.Inner:
                    keyWord = "INNER JOIN";
                    break;
                case JoinType.LeftOuter:
                    keyWord = "LEFT OUTER JOIN";
                    break;
                case JoinType.RightOuter:
                    keyWord = "RIGHT OUTER JOIN";
                    break;
            }

            return string.Format(" {0} {1} ON {2}", keyWord, Visit(expression.Target), Visit(expression.Expression));
        }


        protected virtual string VisitColumnExpression(IColumnExpression expression)
        {
            string column;
            if (!string.IsNullOrEmpty(expression.ColumnPrefix))
                column = string.Format("{0}.{1}", QuoteIdentifier(expression.ColumnPrefix),
                                       QuoteIdentifier(expression.ColumnName));
            else
                column = QuoteIdentifier(expression.ColumnName);            
            return column;
        }

        protected virtual string VisitConstantExpression(IConstantExpression expression)
        {
            if (expression.Value == null)
                return null;
            IDataParameter parameter = _providerFactory.CreateParameter();
            parameter.ParameterName = string.Format("@p{0}", _implicitParameters.Count());
            if (expression.Value.GetType() == typeof(string))
                parameter.DbType = DbType.AnsiString;
            
            parameter.Value = expression.Value;
            
                
            _implicitParameters.Add(parameter.ParameterName, parameter);
            return parameter.ParameterName;
        }

        protected virtual string VisitInExpression(IInExpression inExpression)
        {
            IList<IDataParameter> parameters = new List<IDataParameter>();
            foreach (var value in inExpression.Values)
            {
                IDataParameter parameter = _providerFactory.CreateParameter();
                parameter.ParameterName = string.Format("@p{0}", _implicitParameters.Count());
                parameter.Value = value;
                _implicitParameters.Add(parameter.ParameterName, parameter);            
                parameters.Add(parameter);
            }

            var parameterList = parameters.Select(p => p.ParameterName).Aggregate((current, next) => string.Format("{0}, {1}", current, next));            
            return string.Format("{0} IN ({1})",Visit(inExpression.Target), parameterList);
        }

        protected virtual string VisitExistsExpression(IExistsExpression expression)
        {
            return string.Format("EXISTS({0})", Visit(expression.Target));
        }



        public virtual void Initialize(IServiceRequestResult source)
        {
            _providerName = source.ServiceName;
            _serviceContainer = source.Container;
            _providerFactory = DbProviderFactories.GetFactory(_providerName);
            quoteSuffix = DbProviderFactories.GetFactory(_providerName).CreateCommandBuilder().QuoteSuffix;
            quotePrefix = DbProviderFactories.GetFactory(_providerName).CreateCommandBuilder().QuotePrefix;
        }

        protected virtual string GetBinaryOperator(IBinaryExpression binaryExpression)
        {
            switch (binaryExpression.ExpressionType)
            {
                case BinaryExpressionType.And:
                    return " AND ";
                case BinaryExpressionType.Or:
                    return " OR ";
                case BinaryExpressionType.StartsWith:
                case BinaryExpressionType.EndsWith:
                case BinaryExpressionType.Contains:
                    return " LIKE ";
                case BinaryExpressionType.Equal:
                    return " = ";
                case BinaryExpressionType.NotEqual:
                    return " <> ";
                case BinaryExpressionType.GreaterOrEqual:
                    return " >= ";
                case BinaryExpressionType.GreaterThan:
                    return " > ";
                case BinaryExpressionType.LessThan:
                    return " < ";
                case BinaryExpressionType.LessThanOrEqual:
                    return " <= ";

                case BinaryExpressionType.Add:
                    return " + ";

                case BinaryExpressionType.Subtract:
                    return " - ";

                case BinaryExpressionType.Multiply:
                    return " * ";

                case BinaryExpressionType.Divide:
                    return " / ";
                default:
                    return "";
            }
        }

        protected virtual string GetAggregateOperator(IAggregateExpression aggregateExpression)
        {
            switch (aggregateExpression.ExpressionType)
            {
                case AggregateExpressionType.Avg:
                    return " AVG ";
                case AggregateExpressionType.Count:
                    return " COUNT ";
                case AggregateExpressionType.Max:
                    return " MAX ";
                case AggregateExpressionType.Min:
                    return " MIN ";
                case AggregateExpressionType.Sum:
                    return " SUM ";
                default:
                    return "";
            }
        }
    }
}
